package com.blb.mapper;

import com.blb.entity.*;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface TransferMapper {

    //分页、显示t_move_warehouse里的所有数据
    @Select("SELECT * FROM t_move_warehouse where company_id=#{companyId} limit #{start},#{length}")
    List<Transfer> findAll(int start,int length,String companyId);

    //计算t_move_warehouse有多少条数据
    @Select("SELECT count(id) FROM t_move_warehouse")
    int count();

    //通过仓库id与商品名称搜索转出商品的信息
    List<IomWarehouse> search(String id, String iomGoods);

    //找到所有的仓库名和id
    @Select("select ware_name,id from t_ware where company_id=#{companyId}")
    List<InventoryCheck> findAllWare(String companyId);

    //通过仓库名ware_name找到其id
    @Select("select id from t_ware where ware_name=#{ware}")
    String findWareId(String ware);

    //通过库区名goods_name找到其id
    @Select("select id from t_cargo_area where goods_name=#{area}")
    int findAreaId(String area);

    //通过id找到仓库名
    @Select("select ware_name from t_ware where id=#{id}")
    String findWareName(String id);

    //通过id找到库区名
    @Select("select goods_name from t_cargo_area where id=#{id}")
    String findAreaName(String id);

    //通过仓库id找到所有的库区名和库区id
    @Select("SELECT c.goods_name,c.id FROM t_cargo_area c LEFT JOIN t_ware w ON w.id = c.ware_id WHERE w.id = #{id}")
    List<Wasc> findAllArea(String id);

    //通过库区id找到所有的货架编号和id（货架的标识是id）
    @Select("SELECT s.shelves_no,s.id FROM t_shelves s LEFT JOIN t_cargo_area c ON s.dam_id = c.id WHERE c.id = #{id}")
    List<Wasc> findAllShelves(int id);

    //通过货架编号找到所有的货位编号
    @Select("SELECT c.cargo_no FROM t_cargo c LEFT JOIN t_shelves s ON c.shelves_id = s.id LEFT JOIN t_ware w ON s.ware_id=w.ware_no WHERE s.id=#{id} AND c.status='无货'")
    List<Wasc> findAllCargo(String id);

    /*
    * 移库提交按钮后的sql语句，包括对货位表的修改，移库单号表的添加，出入移详情表的添加
    * */
    /*
    * 首先是对货位表的修改
    * */
    //获取原有货位上的batch(批次号)、product_no(商品编号)、l_code（生产码）通过仓库id，库区id，货架编号，货位编号
    @Select("select batch,product_no,l_code from t_cargo where cargo_no=#{cargo} AND shelves_id =#{shelves} AND dam_id=#{area} AND ware_id=#{ware}")
    Search getBatch(String cargo, String shelves, int area, String ware);

    //通过仓库id，库区id，货架编号，货位编号将product_no、product、batch、status、iom_no、l_code设置为null
    @Update("UPDATE t_cargo t SET product_no=NULL,product=NULL,batch=NULL,STATUS='无货',iom_no=NULL,l_code=NULL WHERE cargo_no=#{cargo} AND shelves_id=#{shelves} AND dam_id=#{area} AND ware_id=#{ware}")
    void emptyById(String cargo,String shelves,int area,String ware);

    //更新新的货位
    @Update("UPDATE t_cargo SET product_no=#{productNo},product=#{product},batch=#{batch},status='有货',iom_no=#{iomNo},l_code=#{lCode} where cargo_no=#{cargo} AND shelves_id=#{shelves} AND dam_id=#{area} AND ware_id=#{ware}")
    void addById(String productNo, String product, int batch, String iomNo, String lCode, String cargo, String shelves, String area, String ware);

    /*
    * 然后是插入一条移库信息数据
    * */
    @Insert("INSERT INTO t_move_warehouse(id,mv_no,mv_out,mv_in,mv_goods,mv_number,mv_mark,mv_person,mv_tel,mv_time,company_id) VALUES(#{id},#{mvNo},#{mvOut},#{mvIn},#{mvGoods},#{mvNumber},#{mvMark},#{mvPerson},#{mvTel},#{mvTime},#{companyId})")
    void insertMove(String id,String mvNo,String mvOut,String mvIn,String mvGoods,int mvNumber,String mvMark,String mvPerson,String mvTel,String mvTime,String companyId);

    /*
    * 接着插入详情表信息
    * */
    @Insert("INSERT INTO t_iom_warehouse(id,iom_goodsno,iom_goods,iom_name,iom_area,iom_shelves,iom_cargoloca,iom_time,iom_no) VALUES(#{id},#{goodsNo},#{goods},#{ware},#{area},#{shelves},#{cargoloca},#{time},#{no})")
    void insertMoveDetails(String id,String goodsNo,String goods,String ware,String area,String shelves,String cargoloca,String time,String no);

    /*
    * 详情部分：分别返回移库表和详情表
    * */
    @Select("select * from t_move_warehouse where id = #{batch}")
    Transfer moveRecord(String batch);

    @Select("select iom_goodsno,iom_goods,iom_name,iom_area,iom_shelves,iom_cargoloca from t_iom_warehouse where iom_name=#{outware} and iom_no=#{id}")
    List<IomWarehouse> outWare(String id,String outware);
}
